package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class CountryDAO {
	
	//writes a pair country bird to the db. If already there, updates
	public static void writeCountry(CountryVO country) throws SQLException{
		
		//first tries to write
		try{
			PreparedStatement comandoInsert = ConnectionManager.getConnection().prepareStatement("INSERT INTO country VALUES (?, ?)");
			comandoInsert.setString(1, country.getBird_scientific_name());
			comandoInsert.setString(2, country.getCountry());
			comandoInsert.executeUpdate();
			
			//if write succeeds then it is over
			return;
			
		}
		
		catch (SQLException ex){
			
		}
		//if it fails, tries updating

		PreparedStatement comandoUpdate = ConnectionManager.getConnection().prepareStatement("UPDATE country SET bird_scientific_name = ?, country_name = ? where bird_scientific_name = ? and country_name = ?");
		comandoUpdate.setString(1, country.getBird_scientific_name());
		comandoUpdate.setString(2, country.getCountry());
		comandoUpdate.setString(3, country.getBird_scientific_name());
		comandoUpdate.setString(4, country.getCountry());
		comandoUpdate.executeUpdate();
	}
	
	//returns all countries that has this bird in it
	public static Vector<CountryVO> selectByBird_scientific_name(String bird_scientific_name) throws SQLException{
		return arbitrarySQL("SELECT * FROM country where bird_scientific_name = '"+bird_scientific_name+"'");
	}
	
	//returns all birds that are in this country
	public static Vector<CountryVO> selectByCountry(String country) throws SQLException{
		return arbitrarySQL("SELECT * FROM country where country_name = '"+country+"'");
	}
	

	
	//deletes all occurrences that have this bird
	public static void deleteByBird(String bird_scientific_name)throws SQLException{
		PreparedStatement comandoDelete = ConnectionManager.getConnection().prepareStatement("DELETE FROM country WHERE bird_scientific_name = ?");
		comandoDelete.setString(1, bird_scientific_name);
		comandoDelete.executeUpdate();
	}
	
	//deletes all occurrences of this country
	public static void deleteByCountry(String country_name)throws SQLException{
		PreparedStatement comandoDelete = ConnectionManager.getConnection().prepareStatement("DELETE FROM country WHERE country_name = ?");
		comandoDelete.setString(1, country_name);
		comandoDelete.executeUpdate();
	}
	
	//deletes a specific association between a country and a bird
	public static void deleteByCountryAndScientificName(String country_name, String bird_scientific_name) throws SQLException{
		PreparedStatement comandoDelete = ConnectionManager.getConnection().prepareStatement("DELETE FROM country WHERE country_name = ? and bird_scientific_name = ?");
		comandoDelete.setString(1, country_name);
		comandoDelete.setString(2, bird_scientific_name);
		comandoDelete.executeUpdate();
	}
	
	
	//deletes an instance of a CountryVO object from the DB
	public static void delete(CountryVO countryObject) throws SQLException{
		deleteByCountryAndScientificName(countryObject.getCountry(), countryObject.getBird_scientific_name());
	}
	
	//Executes an arbitrary SQL command on the Country table
	public static Vector<CountryVO> arbitrarySQL(String sql) throws SQLException{
		Vector<CountryVO> countries = new Vector<CountryVO>();
		ResultSet resultados;
		PreparedStatement comandoSelect = ConnectionManager.getConnection().prepareStatement(sql);
		resultados = comandoSelect.executeQuery();
		
		while(resultados.next()){
			countries.add(new CountryVO(resultados.getString(1), resultados.getString(2)));
		}
		return countries;
	}
}
